package com.dotflat.satistics.repository.crm.impl;

import com.aliyun.openservices.shade.com.alibaba.fastjson.JSON;
import com.dotflat.satistics.dto.FinSatisticMetaDTO;
import com.dotflat.satistics.repository.crm.FinSatisticsRepository;
import com.dotflat.satistics.repository.crm.LeadsRepository;
import com.matt.commons.page.PagerQuery;
import com.matt.repository.base.BaseNativeSqlRepository;
import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.thymeleaf.util.ListUtils;

import java.util.Date;
import java.util.List;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2018/10/22
 * *******************************
 */
@Repository
public class FinSatisticsRepositoryImpl extends BaseNativeSqlRepository implements FinSatisticsRepository {

    private String SqlAndInIds( List<String> clienteleIds){
        if (clienteleIds != null && clienteleIds.size() > 0){
            String param = JSON.toJSONString(clienteleIds);
            param = param.substring(1, param.length()-1);

            return String.format(" AND id IN (%s)", param);
        }

        return null;
    }

    @Override
    public Long countSatisticsForInAndOut(String areaId, List<String> clienteleIds) {
        String idsParam = SqlAndInIds(clienteleIds);

        String sqlFormat = "SELECT count(`id`) FROM `clientele` " +
                            "       WHERE `areaId` = '%s' " +
                            (StringUtils.isNotBlank(idsParam) ? idsParam : "");

        String sql = String.format(sqlFormat, areaId);

        return super.sqlLongResult(sql);
    }
    @Override
    public List<FinSatisticMetaDTO> satisticsForInAndOut(String areaId, List<String> clienteleIds,String beginDate,String endDate ,PagerQuery pagerQuery)  {

        String idsParam = SqlAndInIds(clienteleIds);

        String date = "";
        if(beginDate != null && endDate != null){
            date += String.format(" and cp.created >= '%s'", beginDate);
            date += String.format(" and  cp.created < '%s'", endDate);
        }

        //我的
        String limitString = "";
        if (pagerQuery != null) {
            limitString = String.format(" LIMIT %d, %d ", pagerQuery.getPageNum() * pagerQuery.getPageSize(), pagerQuery.getPageSize());
        }

        String sqlFormat =
                "SELECT c.`id`, c.`childName`, c.`name`, p.id as productId, p.`name` as productName, SUM(cp.`practicalTotalAmount`) as productAmount, SUM(cp.`count`)  as productCount " +
                        "FROM (SELECT `id`,`childName`,`name`,`areaId`,`created` FROM `clientele` " +
                        "       WHERE `areaId` = '%s' " +
                        (StringUtils.isNotBlank(idsParam) ? idsParam : "") +
                        "       ORDER BY `created` DESC " +
                        limitString +
                        ") as c " +
//                        "JOIN `product` p ON p.`areaId` = c.`areaId` AND p.`isVisible` = 1 AND p.`deleted` = 0 " +
                        "LEFT JOIN `fin_pay_order` o ON o.`clienteleId`  = c.`id` and o.payStatus = 1 " +
                        "LEFT JOIN `contract_product` cp ON cp.`contractId` = o.`contractId` " +
                        "LEFT JOIN `product` p ON p.`areaId`= c.`areaId` AND p.id = cp.`productId` " +
                        "   AND p.`isVisible`= 1 " +
                        "   AND p.`deleted`= 0 " +
                        date +
                        "GROUP BY c.id, IFNULL(p.`id`, UUID())" +
                        "ORDER BY c.created DESC, p.created DESC";

        String sql = String.format(sqlFormat, areaId);

        List<FinSatisticMetaDTO> result = super.sqlObjectList(sql, FinSatisticMetaDTO.class);

        return result;
    }
}
